package www.qiyue.kc;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import www.qiyue.utils.UUIDGenerator;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

@RestController
public class Kcyj {
    @Value("${spring.datasource.url}")
    private String url;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @RequestMapping("kcyue")
    public void test1()  {
        Map<String, String> hashMap = new HashMap<>();
        hashMap.put("ais_contract","ais_contract_monthly");
        hashMap.put("ais_house","ais_house_monthly");
        hashMap.put("ais_rent_unit","ais_rent_unit_monthly");
        hashMap.put("ais_fee_plan","ais_fee_plan_monthly");
        hashMap.put("ais_certificatee","ais_certificatee_monthly");
        Set<String> keySet = hashMap.keySet();
        for (String key : keySet) {
            String str="";
            if ("ais_fee_plan".equals(key)){
                str=" and DATE_FORMAT(start_date,'%Y')=DATE_FORMAT('2021-12-31','%Y')";
            }
            String val = hashMap.get(key) + "";
            String keySql="SELECT * from "+key+" where 1=1 "+str;
            List<Map<String, Object>> mapList = jdbcTemplate.queryForList(keySql);

            String sql="select COLUMN_NAME from INFORMATION_SCHEMA.Columns where table_name = '"+val+"'";
            List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
            List<String> column_name = new ArrayList<>();
            for (Map<String, Object> m : list) {
                String columnName = m.get("COLUMN_NAME") + "";
                column_name.add(columnName);
            }
            insertBatch(val,mapList,column_name);
        }
    }

    private boolean insertBatch(String tableName, List<Map<String, Object>> datas,List<String> val) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
        PreparedStatement prepared = null;
        Connection connect = null;
        try {
            //Class.forName(driverClassName);
            connect = DriverManager.getConnection(url, username, password);
            connect.setAutoCommit(false); // 设置手动提交
            StringBuffer fieldString = new StringBuffer();
            StringBuffer paraNo = new StringBuffer(); // 预处理字段“？”
            List<Object> values = new ArrayList<Object>();
            boolean flag = true;
            for (int i = 0; i < datas.size(); i++) {
                for (String s : val) {

                    if (flag) {
                        fieldString.append("," + s);
                        paraNo.append(",?");
                    }
                    if ("monthly".equals(s)) {
                       //String format = sdf.format(new Date());
                        String format = "2021-07";
                        values.add(format);
                    }else
                    values.add(datas.get(i).get(s));
                }
                flag = false;

                if (prepared == null) {
                    // 所有参数组成的数组
                    String queryString = "INSERT INTO " + tableName + " (" + fieldString.toString().substring(1)
                            + ") VALUES (" + paraNo.substring(1) + ")";
                    prepared = connect.prepareStatement(queryString);
                }
                // 设置对应参数值
                for (int j = 0; j < values.size(); j++) {
                    prepared.setObject(j + 1, values.get(j));
                }
                prepared.addBatch();
                if (i % 10000 == 0) {
                    prepared.executeBatch();
                    connect.commit();
                }
                values.clear();
            }
            prepared.executeBatch();
            connect.commit();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            try {
                if (connect != null) {
                    connect.close();
                }
                if (prepared != null) {
                    prepared.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}
